﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Collections;

namespace Garlic.DAL
{
    public class StVoyageAmountsDAL
    {
        public const string ST_VOYAGE_AMOUNTS_SELECT_TABLE =
           @"SELECT 
      A.VAT_ID,
      A.VAT_VOY_ID,
      A.VAT_CSZ_CSIZECD,
      A.VAT_STS_CSTATUSCD,
      A.VAT_DNGFG,
      A.VAT_OVLMTCD,
      A.VAT_RFFG,
      A.VAT_AMOUNT,
      A.VAT_PARTITIONNM,
      A.VAT_ARCHIVETM,
      A.VAT_TYPE,
      A.VAT_EFFG,
      A.VAT_INSERTDT,
      A.VAT_INSERTUSER,
      A.VAT_LSTUPDDT,
      A.VAT_LSTUPUSER,
      A.VAT_ISTOTAL,
      B.CSZ_CSIZENM,
      C.STS_REMARK,
      D.DTP_DNGGNM,
      E.RV_MEANING,
      F.VOY_IEFG
      FROM ST_VOYAGE_AMOUNTS A
      left join st_container_sizes B on B.CSZ_CSIZECD = A.VAT_CSZ_CSIZECD
      left join st_container_status C on C.STS_CSTATUSCD = A.VAT_STS_CSTATUSCD
      left join st_dangerous_cargo_codes D on D.DTP_DNGGCD = A.VAT_DNGFG
      left join st_ref_codes E on E.RV_LOW_VALUE = A.Vat_Ovlmtcd and E.RV_DOMAIN = 'OVLMTCD'
      left join st_voyages F on F.VOY_ID = A.VAT_VOY_ID {0} ";

        /// <summary>
        /// 删除sql语句
        /// </summary>
        public const string ST_VOYAGE_AMOUNTS_DELETE_TABLE = @" DELETE FROM ST_VOYAGE_AMOUNTS  WHERE vat_id=";

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns></returns>
        public DataTable GetTable(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
                query += " where " + textWhere;
            if (!string.IsNullOrEmpty(orderText))
                query += " order by " + orderText;

            return GetDataTable(string.Format(ST_VOYAGE_AMOUNTS_SELECT_TABLE, query));
        }

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
            {
                dt.TableName = "ST_VOYAGE_AMOUNTS";
            }
            return dt;
        }

        /// <summary>
        /// 批量删除表
        /// </summary>
        /// <param name="mList"></param>
        /// <returns></returns>
        public bool DeleteStVoyageAmountsTable(List<string> mList)
        {
            string sql = string.Empty;
            if (mList.Count <= 0)
                return false;

            Database db = DatabaseFactory.CreateDatabase();
            bool isOK = false;
            using (DbConnection conn = db.CreateConnection())
            {
                conn.Open();
                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (string VATID in mList)
                        {
                            sql = string.Empty;
                            sql = string.Format("{0}{1}", ST_VOYAGE_AMOUNTS_DELETE_TABLE, int.Parse(VATID));
                            db.ExecuteNonQuery(tran, CommandType.Text, sql);
                        }

                        tran.Commit();
                        isOK = true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        isOK = false;
                    }
                    
                }
                conn.Close();
            }
            return isOK;
        }

    }//end class
}
